/**
 * 
 */
package model.admin;

import java.sql.ResultSet;
import java.util.ArrayList;

import vo.StudentFinancialsVo;

import myutil.Connect;

/**
 * @author Sony
 * 
 */
public class StudentFinanceMo {

	public ArrayList<StudentFinancialsVo> getStudentFinancials(int student_id) {
		ArrayList<StudentFinancialsVo> list = new ArrayList<StudentFinancialsVo>();
		ResultSet rs;
		int batch_id = 0, degree_department_id = 0, amt_paid = 0;
		String t_id = "";
		String query = "select batch_id,degree_department_id from student_batch where student_id="
				+ student_id;
		//System.out.println(query);
		try {
			Connect c = new Connect();
			rs = c.getResult(query);
			while (rs.next()) {
				batch_id = rs.getInt("batch_id");
				degree_department_id = rs.getInt("degree_department_id");

			}

			query = "select * from degree_current_year_semester where degree_department_id="
					+ degree_department_id;
			//System.out.println(query);
			rs = c.getResult(query);
			ResultSet rs2;
			while (rs.next()) {
				StudentFinancialsVo vo = new StudentFinancialsVo();
				vo.setDescription(rs.getString("description"));
				vo.setYear_semester_id(rs.getInt("year_semester_id"));
				vo.setStudent_id(student_id);
				String query2 = "select * from semesterwise_fees where year_semester_id="
						+ rs.getInt("year_semester_id");
				System.out.println(query2);
				rs2 = c.getResult(query2);
				while (rs2.next()) {
					vo.setTuition_fee(rs2.getInt("tuition_fee"));
					vo.setLibrary_fee(rs2.getInt("library_fee"));
					vo.setHostel_fee(rs2.getInt("hostel_fee"));
					vo.setMess_fee(rs2.getInt("mess_fee"));
					vo.setOther_fee(rs2.getInt("other_fee"));
					vo.setTotal_payment(rs2.getInt("total_payment"));
				}

				String query3 = "select * from student_transactions where student_id="
						+ student_id
						+ " and sem_id="
						+ rs.getInt("year_semester_id");
				System.out.println(query3);
				rs2 = c.getResult(query3);
				if (rs2.next()) {
					rs2.previous();
					while (rs2.next()) {
						t_id = t_id + rs2.getString("t_id")+" , ";
						amt_paid += rs2.getInt("amt_paid");
					}
				} else {
					t_id = "--";
					amt_paid = 0;
				}
				vo.setAmt_paid(amt_paid);
				vo.setT_id(t_id);
				amt_paid=0;
				list.add(vo);
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	public ArrayList<StudentFinancialsVo> getOneTimeFinancials(int student_id) {
		ArrayList<StudentFinancialsVo> list = new ArrayList<StudentFinancialsVo>();
		ResultSet rs;
		int batch_id = 0, degree_department_id = 0, student_batch_id = 0;
		String query = "select student_batch_id,batch_id,degree_department_id from student_batch where student_id="
				+ student_id;
		//System.out.println(query);
		try {
			Connect c = new Connect();
			rs = c.getResult(query);
			while (rs.next()) {
				batch_id = rs.getInt("batch_id");
				student_batch_id = rs.getInt("student_batch_id");
				degree_department_id = rs.getInt("degree_department_id");
			}

			query = "select * from student_finance_one_time where batch_id="
					+ batch_id;
			//System.out.println(query);
			rs = c.getResult(query);
			while (rs.next()) {
				StudentFinancialsVo vo = new StudentFinancialsVo();
				vo.setStudent_batch_id(rs.getInt("batch_id"));
				vo.setCaution_fee(rs.getInt("caution_fee"));
				vo.setAdmission_fee(rs.getInt("admission_fee"));
				vo.setOther_fee(rs.getInt("other_fee"));
				vo.setTotal_payment(rs.getInt("total_payment"));
				vo.setDescription(rs.getString("description"));
				vo.setStudent_id(student_id);
				String query2 = "select * from student_transactions where student_id="
						+ student_id + " and sem_id=0";
				ResultSet rs2 = c.getResult(query2);
				String t_id = "";
				int amt_paid = 0;
				if (rs2.next()) {
					rs2.previous();
					while (rs2.next()) {
						t_id = t_id + rs2.getString("t_id")+" , ";
						amt_paid += rs2.getInt("amt_paid");
					}
				} else {
					t_id = "--";
					amt_paid = 0;
				}
				vo.setAmt_paid(amt_paid);
				vo.setT_id(t_id);

				list.add(vo);
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	public ArrayList<StudentFinancialsVo> getSemFinancials(int student_id,
			int year_semester_id) {
		ArrayList<StudentFinancialsVo> list = new ArrayList<StudentFinancialsVo>();
		ResultSet rs;
		int batch_id = 0, degree_department_id = 0, amt_paid = 0;
		String t_id = "";
		String query = "select batch_id,degree_department_id from student_batch where student_id="
				+ student_id;
		//System.out.println(query);
		try {
			Connect c = new Connect();
			rs = c.getResult(query);
			while (rs.next()) {
				batch_id = rs.getInt("batch_id");
				degree_department_id = rs.getInt("degree_department_id");

			}

			query = "select * from degree_current_year_semester where degree_department_id="
					+ degree_department_id
					+ " and year_semester_id="
					+ year_semester_id;
			//System.out.println(query);
			rs = c.getResult(query);
			ResultSet rs2;
			while (rs.next()) {
				StudentFinancialsVo vo = new StudentFinancialsVo();
				vo.setDescription(rs.getString("description"));
				vo.setYear_semester_id(rs.getInt("year_semester_id"));
				vo.setStudent_id(student_id);
				String query2 = "select * from semesterwise_fees where year_semester_id="
						+ year_semester_id;
				//System.out.println(query2);
				rs2 = c.getResult(query2);
				while (rs2.next()) {
					vo.setTuition_fee(rs2.getInt("tuition_fee"));
					vo.setLibrary_fee(rs2.getInt("library_fee"));
					vo.setHostel_fee(rs2.getInt("hostel_fee"));
					vo.setMess_fee(rs2.getInt("mess_fee"));
					vo.setOther_fee(rs2.getInt("other_fee"));
					vo.setTotal_payment(rs2.getInt("total_payment"));
				}

				String query3 = "select * from student_transactions where student_id="
						+ student_id
						+ " and sem_id="
						+ rs.getInt("year_semester_id");
				System.out.println(" amt_paid="+amt_paid+" : " +query3+"");
				rs2 = c.getResult(query3);
				if (rs2.next()) {
					rs2.previous();
					while(rs2.next()){
					t_id = t_id + rs2.getString("t_id");
					amt_paid += rs2.getInt("amt_paid");}
				} else {
					t_id = "--";
					amt_paid = 0;
				}
				vo.setAmt_paid(amt_paid);
				vo.setT_id(t_id);
				amt_paid=0;
				list.add(vo);
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	public void insertOnetimeTransaction(String t_id, int amt_paid,
			int student_id,String type) throws Exception {
		Connect c = new Connect();
		ResultSet rs;
		String query;
		int acc_id;
		query = "select acc_id from student_account where student_id="
				+ student_id;
		//System.out.println(query);
		try {
			rs = c.getResult(query);

			if (rs.next()) {
				acc_id = rs.getInt("acc_id");
			} else {
				throw new Exception("account id not given..!! Sorry..");
			}

			query = "insert into student_transactions(t_id,student_id,acc_id,sem_id,amt_paid) values('"
					+ t_id
					+ "',"
					+ student_id
					+ ","
					+ acc_id
					+ ",0,"
					+ amt_paid + ")";

			//System.out.println(query);
			c.dml(query);
			c.logger(type, "Insert");
		} catch (Exception e) {

			e.printStackTrace();
			throw new Exception(
					"transaction detail already exist.. please edit in transaction details");
		}
	}

	public int getTotalFeePaid(int student_id) {
		int amt_paid=0;
		String query="select * from student_transactions where student_id="+student_id;
		Connect c=new Connect();
		try {
			ResultSet rs=c.getResult(query);
			
			while(rs.next())
			{
				amt_paid+=rs.getInt("amt_paid");
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return amt_paid;
	}

	public int getTotalFee(int student_id) {
		
		int total_fee=0;
		String query1="select batch_id from student_batch where student_id="+student_id;
		int batch_id = 0;
		Connect c=new Connect();
		try {
			ResultSet rs=c.getResult(query1);			
			while(rs.next())
			{
				batch_id=rs.getInt("batch_id");
			}
			String query2="select total_payment from semesterwise_fees where batch_id="+batch_id;
			ResultSet rs2=c.getResult(query2);			
			while(rs2.next())
			{
				total_fee+=rs2.getInt("total_payment");
			}
			String query3="select total_payment from student_finance_one_time where batch_id="+batch_id;
			ResultSet rs3=c.getResult(query3);			
			while(rs3.next())
			{
				total_fee+=rs3.getInt("total_payment");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return total_fee;
	}

	public ArrayList<StudentFinancialsVo> getAllTransactions(int student_id) {
		ArrayList<StudentFinancialsVo> list = new ArrayList<StudentFinancialsVo>();
		Connect c=new Connect();
		String query="select * from student_transactions where student_id="+student_id;
		
		try {
			ResultSet rs=c.getResult(query);
			while(rs.next())
			{
				StudentFinancialsVo vo=new StudentFinancialsVo();
				vo.setT_id(rs.getString("t_id"));
				vo.setSem_id(rs.getInt("sem_id"));
				vo.setAmt_paid(rs.getInt("amt_paid"));
				vo.setStudent_id(student_id);
				vo.setAcc_id(rs.getInt("acc_id"));
				list.add(vo);
				
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return list;
		
	}

	public ArrayList<StudentFinancialsVo> getSemTransactions(int student_id,int year_semester_id) {
		ArrayList<StudentFinancialsVo> list = new ArrayList<StudentFinancialsVo>();
		Connect c=new Connect();
		String query="select * from student_transactions where student_id="+student_id+" and sem_id="+year_semester_id;
		
		try {
			ResultSet rs=c.getResult(query);
			while(rs.next())
			{
				StudentFinancialsVo vo=new StudentFinancialsVo();
				vo.setT_id(rs.getString("t_id"));
				vo.setSem_id(rs.getInt("sem_id"));
				vo.setAmt_paid(rs.getInt("amt_paid"));
				vo.setAcc_id(rs.getInt("acc_id"));
				vo.setStudent_id(student_id);
				list.add(vo);
				
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return list;
	}

	public ArrayList<StudentFinancialsVo> getTransactionDetails(String t_id) {
		ArrayList<StudentFinancialsVo> list = new ArrayList<StudentFinancialsVo>();
		Connect c=new Connect();
		String query="select * from student_transactions where t_id='"+t_id+"'";
		
		try {
			ResultSet rs=c.getResult(query);
			while(rs.next())
			{
				StudentFinancialsVo vo=new StudentFinancialsVo();
				vo.setT_id(rs.getString("t_id"));
				vo.setSem_id(rs.getInt("sem_id"));
				vo.setAmt_paid(rs.getInt("amt_paid"));
				vo.setAcc_id(rs.getInt("acc_id"));
				vo.setStudent_id(rs.getInt("student_id"));
				list.add(vo);
				
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return list;
	}

	public void deleteTransaction(String single_t_id,String type) {
		Connect c=new Connect();
		String query="delete from student_transactions where t_id='"+single_t_id+"'";
		try {
			c.dml(query);
			c.logger(type, "Delete");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

	public void insertSemwiseTransaction(String t_id, int amt_paid,int student_id, int year_semester_id,String type) throws Exception {
		Connect c = new Connect();
		ResultSet rs;
		String query;
		int acc_id;
		query = "select acc_id from student_account where student_id="
				+ student_id;
		System.out.println(query);
		try {
			rs = c.getResult(query);

			if (rs.next()) {
				acc_id = rs.getInt("acc_id");
			} else {
				throw new Exception("account id not given..!! Sorry..");
			}

			query = "insert into student_transactions(t_id,student_id,acc_id,sem_id,amt_paid) values('"
					+ t_id+ "',"+ student_id+ ","+ acc_id+ ","+year_semester_id+","+ amt_paid + ")";

			//System.out.println(query);
			c.dml(query);
			c.logger(type, "Insert");
		} catch (Exception e) {

			e.printStackTrace();
			throw new Exception("transaction detail already exist.. please edit in transaction details");
		}
		
	}

}
